package com.mymaxis.service;

import com.checkfree.isolutions.customer.persist.Account;
import com.checkfree.isolutions.customer.persist.AccountHomeImpl;
import com.checkfree.isolutions.customer.persist.Merchant;
import com.checkfree.isolutions.customer.persist.MerchantHomeImpl;
import com.checkfree.isolutions.persist.PersistServer;
import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;
import com.checkfree.isolutions.statement.persist.Statement;
import com.checkfree.isolutions.statement.persist.StatementHomeImpl;
import com.mymaxis.domain.AccountSubInfo;
import com.mymaxis.domain.UpdateSubscriptionInfoResponse;
import com.mymaxis.ebppx.EbppxAcctDetailsDataBean;
import com.mymaxis.ebppx.EbppxConstants;
import com.mymaxis.ebppx.EbppxEcsUser;
import com.mymaxis.manager.DBConnectionManager;
import com.mymaxis.utils.DateUtil;
import com.mymaxis.utils.EmailNotification;
import com.mymaxis.utils.EncryptionHelper;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EmailSubscriptionInfo
{
  private final DateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
  public static final String passphrase = "X2q3j5k6E7r8l93tm2n7H5qp";

  public EbppxAcctDetailsDataBean getSubscriptionInfo(String accNo, String updateByUser, String updateByChannel)
  {

    EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();

    Connection connection1 = null;
    Connection connection = null;
    Date dateRegisterDate = null;
    Date dateExpiryDate = null;
    Date strRegisterDate = null;
    Date currentDate = new Date();
    String emailStatus = "";
    String status = "";
    try
    {
      String merchant = "Maxis";

      PersistServer.activate(merchant);
      PersistServer.beginTransaction();
      String strAcctId = AcctDetails.getAcctId(accNo);
      String merchantName = getMerchant(); 
      String strPhysicalBillSubscription = "";
      String email = "";
      connection =  DBConnectionManager.getDbConnection();
      
      
      if ((strAcctId == null) || (strAcctId.equals("")))
      {
        Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName(merchant);
        Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(accNo, cfiMerchant);

        if (account != null) {
          System.out.println("Found in checkfree Db ,add to accountDetails");
          AcctDetails.insertAcctDetails(accNo, accNo, "", "");
          acctDataBean = AcctDetails.getAcctDetails(accNo);

         
        }
        else
        {
          acctDataBean.setAcctId(accNo);
        }

      }
      else
      {
     
    	  acctDataBean = AcctDetails.getLatestEmailDeliveryStatus(connection, accNo);
    	  Date dateRegisteredDate = acctDataBean.getRegisterDate();    
    	  emailStatus = acctDataBean.getEmailStatus();
    
    	  connection1 = DBConnectionManager.getKenanDbConnection();
          //get KenanConnection
          String strCategoryDatabase = AcctDetails.getCategoryDatabase(connection1);
          String cusDatabase = AcctDetails.getCusDatabase(connection1, strCategoryDatabase, accNo);

          connection1.setAutoCommit(false);
    	  String kenanAccounNo = AcctDetails.getKenanAccountNo(connection1, strCategoryDatabase, accNo);
          System.out.println("KenanAcctNO :"+kenanAccounNo);

          String strTemp = AcctDetails.processCUSPhysicalBillSubscription(connection1,cusDatabase,accNo,strCategoryDatabase);
         
    
    
    		if((emailStatus.equals("Update")) ||(emailStatus.equals("Subscription"))){
    			
    		    dateRegisterDate = acctDataBean.getRegisterDate();
    			if (dateRegisterDate != null) {
    				
    				
    				dateExpiryDate = dateRegisterDate;

    				Calendar c = Calendar.getInstance();
    				c.setTime(dateExpiryDate);
    				c.add(Calendar.DATE, 5);
    				dateExpiryDate = c.getTime();
    				System.out.print("dateExpiryDate :"+dateExpiryDate);
    				System.out.print("currentDate :"+currentDate);
    				if (currentDate.after(dateExpiryDate)) {
    					
    					 acctDataBean =AcctDetails.getAcctDetails(strAcctId);
    					
    				 
    				
    			     }else{
    				     status = "true";	
						 acctDataBean.setEmailStatus("P");
    			    	 acctDataBean.setSMSNo(AcctDetails.getSMSNo(strAcctId));
    			    	 acctDataBean.setSMSSubscription(AcctDetails.getSMSSubscription(strAcctId));
      			
    			     }
    			}
    				
    		}else{
    		
    			acctDataBean =AcctDetails.getAcctDetails(strAcctId);
    		
    		}
    	  
            acctDataBean.setAcctId(accNo);

            if (acctDataBean == null){
        	
              acctDataBean.setAcctId(accNo);
             }

      }

      PersistServer.commitTransaction();
    } catch (Throwable e) {
     
      try {
    	  if (PersistServer.transactionIsActive()) {
				try {
					PersistServer.rollbackTransaction();
				} catch (Exception er) {
				}
			}
			PersistServer.deactivate(); // clean up with deactivate
			System.out.println("Deactivate the transaction.....");
      }
      catch (Exception localException)
      {
      }
      
    }
    finally
    {
      try
      {
       
    	  if(connection != null){
    		  
    		  DBConnectionManager.freeDbConnection(connection);
    		  
    	  }
    	  if(connection1 != null){
    		  
    		  DBConnectionManager.freeKenanDbConnection(connection1);
    		  
    	  }
    	  if (PersistServer.transactionIsActive()) {
				try {
					PersistServer.rollbackTransaction();
				} catch (Exception er) {
				}
			}
    	  
			PersistServer.deactivate(); // clean up with deactivate
			System.out.println("Deactivate the transaction.....");
			
      }catch (Throwable localThrowable2)
      {
      }
    }
    return acctDataBean;
  }

  public String getMerchant()
  {
    String merchantName = "";
    try {
      Properties appCfg = PropertiesConfig.getApplicationSettings();

      if (appCfg.hasProperty("MerchantName"))
        merchantName = appCfg.getProperty("MerchantName");
    }
    catch (Exception e)
    {
      System.out.println(e);
    }
    return merchantName;
  }

  public UpdateSubscriptionInfoResponse UpdateSubscriptionInfo(String emailAddrs, String emailBillSubscript, String acctNo, String hcSuprssSubscript, String smsAlertFlag, String smsNo, String updateByUser, String updateByChannel)
  {
    String passphrase = "X2q3j5k6E7r8l93tm2n7H5qp";
    EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();
    EbppxAcctDetailsDataBean acctDataBean2 = new EbppxAcctDetailsDataBean();
    AccountSubInfo acctSubInfo = new AccountSubInfo();
    UpdateSubscriptionInfoResponse updateResponse = new UpdateSubscriptionInfoResponse();
    EmailNotification emailObj = new EmailNotification();
    AcctDetails acctdetails = new AcctDetails();

    
    boolean booleanEmailAddress = false;
    boolean booleanEmailSubscription = false;
    boolean booleanStopHardCopy = false;
    boolean booleanAccountFound = true;
    String failMsgStopHardCopy = "";
    String failMsgEmailSubscription = "";
    String failMsgEmailAddress = "";
    String strRegisterDateDb = "";
    String emailRegisterStatus = "";
    String emailAddresFrmLog = "";
    String statusFlag = "";
    String statusEmailUnscript = "";
	String statusUnscript ="";
    boolean infoUpdatedFlag = false;
	String strEmailFromLog = "";
    String emailEncrypt = null;
    String acctNoEncrypt = null;
    String acctNameEncrypt = null;

    String failMsg = "";
    List list = new ArrayList();

    String EMAIL_PATTERN = "^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$";
    String NUMBER_PATTERN = "^[_0-9]";

    String accountName = null;
    String updateStatus = "FAIL";
    String strDate = "";
    String strLastModifyDate = "";
    Date registerDateDB = null;
    String strRegisterDateNw = "";
    Connection connection1 = null;
    Connection connection = null;
    try
    {
      PersistServer.activate("Maxis");
      PersistServer.beginTransaction();
      strDate = DateUtil.formatDate(new Date());
      strLastModifyDate = DateUtil.formatDate(new Date());
    
      connection =  DBConnectionManager.getDbConnection();
      connection1 = DBConnectionManager.getKenanDbConnection();
      //get KenanConnection
      String strCategoryDatabase = AcctDetails.getCategoryDatabase(connection1);
      String cusDatabase = AcctDetails.getCusDatabase(connection1, strCategoryDatabase, acctNo);

      connection1.setAutoCommit(false);
      connection.setAutoCommit(false);

     
      System.out.println("UpdatedByChannel :" + updateByChannel);

      String status = "";
      boolean updateStatusFlag = false;
      boolean emailUpdateFlag = false;
	  boolean SubscriptUpdatedFlag = false;
      String emailSentStatus = "";
      

      String strEmailDB = "";
      String emailLogAction = "";
      String emailSubDB = "";
      String acctSubDB = "";
      String emailAddrDB = "";
      String smsSubNw = "";
      String smsNoDB = "";
      String emailSubNw = emailBillSubscript;
      String acctSubNw = hcSuprssSubscript;
      String strPhysicalBillSubscription = "";
      String emailAddrNw = emailAddrs;
      String emailSub = emailBillSubscript;
      String smsSub = smsAlertFlag;
      String acctSub = hcSuprssSubscript;
      

      System.out.println("emailAddrNw :" + emailAddrNw);
      System.out.println("emailSub :" + emailSub);
      System.out.println("smsSub :" + smsSub);
      System.out.println("smsNo :" + smsNo);
      System.out.println("acctSub :" + acctSub);
      System.out.println("hcSuprssSubscript :" + hcSuprssSubscript);
      System.out.println("UpdateByUser    :" + updateByUser);
      System.out.println("UpdateByChannel :" + updateByChannel);
      Date dtUpdateDate = new Date();
      String dateString = DateUtil.formatDate(dtUpdateDate);

      if ((acctNo != null) && (!acctNo.equalsIgnoreCase("")))
      {
        if ((!hcSuprssSubscript.equalsIgnoreCase("")) && (hcSuprssSubscript != null))
        {
          if ((hcSuprssSubscript.equalsIgnoreCase("Y")) || (hcSuprssSubscript.equalsIgnoreCase("N")))
          {
            booleanStopHardCopy = true;
          }
          else {
            failMsgStopHardCopy = "Invalid Flag StopHardCopy";
            list.add(failMsgStopHardCopy);
          }
        }
        else
        {
          failMsgStopHardCopy = "Invalid Flag StopHardCopy";
          list.add(failMsgStopHardCopy);
        }

        if ((!emailBillSubscript.equalsIgnoreCase("")) && (emailBillSubscript != null))
        {
          if ((emailBillSubscript.equalsIgnoreCase("Y")) || (emailBillSubscript.equalsIgnoreCase("N")))
          {
            booleanEmailSubscription = true;
          }
          else {
            failMsgEmailSubscription = "Invalid Flag EmailSubscription";
            list.add(failMsgEmailSubscription);
          }
        }
        else {
          failMsgEmailSubscription = "Invalid Flag EmailSubscription";
          list.add(failMsgEmailSubscription);
        }

        if (emailBillSubscript.equalsIgnoreCase("Y"))
        {
          if ((!emailAddrs.equalsIgnoreCase("")) && (emailAddrs != null) && (emailAddrs != "?"))
          {
            Pattern p = Pattern.compile(EMAIL_PATTERN);
            Matcher m = p.matcher(emailAddrs);

            if (m.find())
            {
              booleanEmailAddress = true;
            }
            else
            {
              failMsgEmailAddress = "Invalid Email Address";
              list.add(failMsgEmailAddress);
            }

          }
          else
          {
            failMsgEmailAddress = "Invalid Email Address";
            list.add(failMsgEmailAddress);
          }

        }
        else
        {
          booleanEmailAddress = true;
        }

        System.out.println("list:" + list);

        System.out.println("list size:" + list.size());

        if (list.size() != 0)
        {
          for (int i = 0; i < list.size(); i++)
          {
            if ((list.size() == 1) || (list.size() == i + 1))
            {
              failMsg = failMsg + list.get(i).toString();
            }
            else
            {
              failMsg = failMsg + list.get(i).toString() + ",";
            }

          }

        }

        if ((booleanEmailSubscription) && (booleanStopHardCopy) && (booleanEmailAddress))
        {
          String strAcctId = AcctDetails.getAcctId(acctNo);
          System.out.println("strAcctId found :" + strAcctId);
          if ((strAcctId == null) || (strAcctId.equals("")))
          {
            Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName("Maxis");
            Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(acctNo, cfiMerchant);
            if (account != null) {
              System.out.println("Found in checkfree Db ,add to accountDetails");
              AcctDetails.insertAcctDetails(acctNo, acctNo, "", "");
              booleanAccountFound = true;
            }
            else
            {
              EbppxEcsUser ecsUser = new EbppxEcsUser();
              ecsUser.setAccount(acctNo);
              Vector cfiAccoountunts = ecsUser.getAccounts();
              AcctDetails.insertAcctDetails(acctNo, acctNo, "", "");
              booleanAccountFound = true;
            }

          }

          if (booleanAccountFound)
          {
        	  
            acctDataBean = AcctDetails.getAcctDetails(acctNo);
            acctDataBean2 = AcctDetails.getLatestEmailDeliveryStatus(connection, acctNo);
            
            emailRegisterStatus = acctDataBean2.getEmailStatus();
            
            
        	
            System.out.println("emailRegisterStatus : "+emailRegisterStatus);
            if(!emailRegisterStatus.equals("")){ //dunt have any log inside emailEbillLog = new user
   
            	if(emailRegisterStatus.equals("Verification")||emailRegisterStatus.equals("unsubscription")){
            	
            	
            		infoUpdatedFlag = true;
            		SubscriptUpdatedFlag = true;
            		strEmailFromLog = acctDataBean.getEmailAdd();
            	
            	}
            	if((emailRegisterStatus.equals("Subscription")) || (emailRegisterStatus.equals("Update"))){
            		
            		//user already subscription ebill and want to update email address
            		emailAddresFrmLog =acctDataBean2.getEmailAdd();
            		strEmailFromLog = emailAddresFrmLog;
            		infoUpdatedFlag = true;
            		System.out.println("user cannot can update");
            		
            	}
            }else{
            	
            	System.out.println("Dunt have email log : first User ");
            	strEmailFromLog = acctDataBean.getEmailAdd();
            	infoUpdatedFlag = false;
            	SubscriptUpdatedFlag = false;
            	
            }
            strEmailDB = acctDataBean.getEmailAdd();
            emailSubDB = acctDataBean.getEmailSubscription();
            registerDateDB = acctDataBean.getRegisterDate();

            if (registerDateDB != null) {
              strRegisterDateNw = this.df.format(registerDateDB);
            }

        
            emailAddrDB = acctDataBean.getEmailAdd();
     
            if (emailAddrNw != null)
            {
              emailEncrypt = EncryptionHelper.encrypt(emailAddrNw, "X2q3j5k6E7r8l93tm2n7H5qp");
             
            }

            if (acctNo != null)
            {
              acctNoEncrypt = EncryptionHelper.encrypt(acctNo, "X2q3j5k6E7r8l93tm2n7H5qp");
            }
            
            Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName("Maxis");
            Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(acctNo, cfiMerchant);
            Statement cfiStatement = null;
            
            if ((account != null) && (!account.equals(""))) {
            	
            	cfiStatement = getStatementHome().findTheLatestStatementForAccount(account);
            
            }

            if (cfiStatement != null) {
              accountName = cfiStatement.getUserField1();
            }
            else {
              accountName = "Customer";
            }
            

            if (accountName != null)
            {
              acctNameEncrypt = EncryptionHelper.encrypt(accountName, "X2q3j5k6E7r8l93tm2n7H5qp");
            }

      
            if (registerDateDB != null)
            {
              strRegisterDateDb = this.df.format(registerDateDB);
            }

            
            if (emailSub != null) {
            	
              if (emailSub.equalsIgnoreCase("Y"))
              {
                emailSubNw = "1";
              }
              else if (emailSub.equalsIgnoreCase("N"))
              {
                emailSubNw = "0";
              }
            }

            if (acctSub != null)
            {
              if (acctSub.equalsIgnoreCase("Y"))
              {
                acctSubNw = "1";
              }
              else if (acctSub.equalsIgnoreCase("N"))
              {
                acctSubNw = "0";
              }
            }

            if (smsSub != null) {
              if (smsSub.equalsIgnoreCase("Y"))
              {
                smsSubNw = "1";
              }
              else if (smsSub.equalsIgnoreCase("N"))
              {
                smsSubNw = "0";
              }

            }
            
            if((!infoUpdatedFlag) && (!SubscriptUpdatedFlag)){
				
				if((emailAddrDB != null) && (emailAddrDB != "")){
					
					if(!emailAddrDB.equals(emailAddrNw)){
					   
						emailUpdateFlag = true;
					}	
				}
			
		    	if((emailSubDB != null) && (emailSubDB != "")){
					
		    		if(!emailSubDB.equals("1")){
		    		
		    			SubscriptUpdatedFlag = true;
		    		}
					
		    	}
			}

            String kenanAccounNo = AcctDetails.getKenanAccountNo(connection1, strCategoryDatabase, acctNo);
            System.out.println("KenanAcctNO :"+kenanAccounNo);

        
   
        	// If there is a changes in the subscription bit for SMS notification
			if(!smsSub.equals(smsSubNw)){
				if (smsSub.equals("0") && smsSubNw.equals("1") && !smsSubNw.equals("")){
					//AcctDetails.logActivity(connection, stmt, EbppxConstants.SMS_NOTIFY_SUBSCRIPTION, acctNo, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strSmsNoNew, "", "", "");
			
				}else if (smsSub.equals("1") && smsSubNw.equals("0")){
					//AcctDetails.logActivity(connection, stmt, EbppxConstants.SMS_NOTIFY_UNSUBSCRIPTION,strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strSmsNoNew, "", "", "");
				
				}
			
				AcctDetails.updateSMSSubscription(connection, acctNo,smsSubNw);
				acctDataBean.setSMSSubscription(smsSubNw);
			}
			
			if((smsNoDB != smsNo) && (smsSubNw.equals("1"))){
				
				AcctDetails.updateSMSNo(connection, acctNo, smsNo);
				acctDataBean.setSMSNo(smsNo);
				//EbppxCusDBUtil.logActivity(connection, stmt, EbppxConstants.SMS_NO_CHANGE, strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strSmsNoNew, strSmsNoDB, "", "");
			}
			
			
            if(((!emailAddrNw.equals(strEmailFromLog)) && infoUpdatedFlag) || (emailUpdateFlag)){
        		
            	//AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_CHANGE_DELIVERY, acctNo, updateByUser, EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrNw, strEmailFromLog, "", "");
            	if(emailAddrDB.equals("")){
            		
            		AcctDetails.logUserActivity("AC001", acctNo, "user", updateByChannel, "CHANGED", strLastModifyDate, "", emailAddrNw, strEmailFromLog, "", "");
            	}else{
            		AcctDetails.logUserActivity("AC001", acctNo, "user", updateByChannel, "CHANGED", strLastModifyDate, "", emailAddrNw, emailAddrDB, "", "");
            	}
            	statusFlag="true";
				status="subscript"; 
				System.out.println("EMAIL CHANGED");
						
		
            }

        
            if(!AcctDetails.checkIfPhysicalBillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){//this is used to check if the data exist in the info_cust_text table
					
				System.out.println("Kenan for phsical no exits ");
				if(!emailSubDB.equals(emailSubNw)){
					
					AcctDetails.insertInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION,kenanAccounNo);
						
				}
				
				
				
			}
     
           if(AcctDetails.checkIfEbillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){//this is used to check if the data exist in the info_cust_text table
				// If there is a changes in the subscription bit for Email bill delivery
				
					if ((emailSubDB.equals("0"))&&(!emailAddrNw.equals("")) && (!emailSubNw.equals("0"))&&(SubscriptUpdatedFlag)){
						System.out.println("EMAIL SUBSCRIPT ");
						status="subscript"; 
						statusFlag="true";
						AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrNw, "", "", "EMAILSUBWEB");
						//AcctDetails.logActivity(connection, stmt, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strEmailAddrNew, "", "", "");
						acctDataBean.setEmailSubscription(emailSubNw);
					}else if ((emailSubNw.equals("0"))){
						System.out.println("EMAIL UNSUBSCRIPT ");
						statusEmailUnscript="true";
						//strNewPhysicalBillSubscription = "0";
						//EbppxCusDBUtil.logActivity(connection, stmt, EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION,strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strEmailAddrNew, "", "", "");		
						statusUnscript="unsubscription";
						//EbppxCusDBUtil.logActivity(connection, stmt, EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION, strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strSmsNoNew, "", "", "");
						AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrDB, "", "", "EMAILUNSUBWEB");
						if((!emailAddrDB.equals("")) && (!emailSubDB.equals("0")) &&(!emailSubDB.equals(""))){
							
							if (!updateByChannel.equals("SSO")){
								
								String emailDBEncrypt = EncryptionHelper.encrypt(strEmailDB,passphrase);
					       		emailSentStatus = emailObj.sendEmailNotification(emailDBEncrypt,strEmailDB,acctNoEncrypt,acctDataBean.getAcctId(),"",statusUnscript,accountName,acctNameEncrypt,emailLogAction,updateByChannel);
							}else{
								AcctDetails.logEmailDelivery(connection,acctNo, strEmailFromLog,dateString, updateByUser, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION_VALUE,updateByChannel);
							}
						}else{
							
							AcctDetails.logEmailDelivery(connection,acctNo, strEmailFromLog,dateString, updateByUser, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION_VALUE,updateByChannel);
						
							
						   
						}
						
						acctDataBean.setEmailSubscription(emailSubNw);
						AcctDetails.updateAcctInfo(connection, acctNo, "0", dateString,"", updateByChannel, updateByUser);
				    	AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION, kenanAccounNo);
				    	AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION, kenanAccounNo);
					}
			
		  }else{//doesn't exist,insert a new record in info_cust_text
				// If there is a changes in the subscription bit for Email bill delivery
			   if(!emailSubDB.equals(emailSubNw)){
					
						status="subscript";
						statusFlag="true";
						AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrDB, "", "", "");
						AcctDetails.insertInfoCustText(connection1, cusDatabase,EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION,kenanAccounNo);
						acctDataBean.setEmailSubscription(emailSubNw);
					
				}else{
					acctDataBean.setEmailSubscription(emailSubNw);
				}
			}
			
   
            
            if (status == "subscript")
            {
             
            	if (!updateByChannel.equals("SSO"))
            	{
            	  
            			if((infoUpdatedFlag ||emailUpdateFlag)){
            	 		
            				emailLogAction="update";
            			
            			}
            			emailSentStatus = emailObj.sendEmailNotification(emailEncrypt, emailAddrNw, acctNoEncrypt, acctNo, updateByUser, status, accountName, acctNameEncrypt, emailLogAction, updateByChannel);

            			System.out.println("emailSentStatus :" + emailSentStatus);

               
                  }
                 else {
            	  		System.out.println(" Update by SSO only ");
            	  		emailSubNw = "1";
            	  		emailSentStatus = "success";
            	  		System.out.println("updateByChannel :"+updateByChannel);
            	  		AcctDetails.logEmailDelivery(connection,acctNo, emailAddrNw,dateString, updateByUser, EbppxConstants.CUST_VERIFICATION_ACTION, EbppxConstants.CUST_VERIFICATION_ACTION_ACTION,updateByChannel);
            	  		updateStatusFlag = AcctDetails.updateAllAcctDetailsCsr(connection, acctNo, emailSubNw, smsSubNw, emailAddrNw, smsNo, dateString, dateString, updateByUser, updateByChannel);
            	  				
            	  		//AcctDetails.updateAllAcctDetailsCsr(connection, acctNo, emailSubNw, smsSubNw, emailAddrNw, smsNo, dateString, dateString, updateByUser, updateByChannel);
            	  		                   
            	  		AcctDetails.updateInfoCustText(connection1, cusDatabase, EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION, kenanAccounNo);
            	  		AcctDetails.updateInfoCustText(connection1, cusDatabase, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, kenanAccounNo);
                  
                 
            	  	
              		}
            	}
            
      

          }
          else
          {
            failMsg = failMsg + "Account No found";
          }
        }
      }
      else {
        failMsg = failMsg + "Account No is empty";
      }
      System.out.println("emailSentStatus :" + emailSentStatus);
      System.out.println("updateStatusFlag :" + updateStatusFlag);
      connection.commit();
      connection.setAutoCommit(true);
      connection1.commit();
      connection1.setAutoCommit(true);
      updateStatus = "SUCC";
      PersistServer.commitTransaction();
    } catch (Throwable e) {
      e.printStackTrace();
      updateStatus = "FAIL";
      System.out.println(failMsg);
      try
      {
        PersistServer.rollbackTransaction();
      } catch (Exception ex) {
        ex.printStackTrace();
      }

      updateResponse.setExtAcctNo(acctNo);
      updateResponse.setUpdateStatus(updateStatus);
      updateResponse.setFailMsg(failMsg.toString());
      try
      {
        if (connection1 != null) {
           DBConnectionManager.freeKenanDbConnection(connection1);
         
        }
        if(connection != null){
        	
          DBConnectionManager.freeDbConnection(connection);
        }
        if (PersistServer.transactionIsActive()) {
			try {
				PersistServer.rollbackTransaction();
			} catch (Exception er) {
			}
			PersistServer.deactivate(); // clean up with deactivate
		}
	
		System.out.println("Deactivate the transaction.....");
        
      }
      catch (SQLException localSQLException)
      {
      }
      catch (Throwable localThrowable1)
      {
      }
    }
    finally
    {
      updateResponse.setExtAcctNo(acctNo);
      updateResponse.setUpdateStatus(updateStatus);
      updateResponse.setFailMsg(failMsg.toString());
      try
      {
        if (connection1 != null) {
          DBConnectionManager.freeKenanDbConnection(connection1);
          DBConnectionManager.freeDbConnection(connection);
        }
        if (PersistServer.transactionIsActive()) {
			try {
				PersistServer.rollbackTransaction();
			} catch (Exception er) {
			}
		}
		PersistServer.deactivate(); // clean up with deactivate
		System.out.println("Deactivate the transaction.....");
      }
      catch (SQLException localSQLException1)
      {
      }
      catch (Throwable localThrowable2) {
      }
    }
    return updateResponse;
  }

  public StatementHomeImpl getStatementHome()
  {
    return StatementHomeImpl.singleton();
  }

  public void CheckEmailSubExpiry(EbppxAcctDetailsDataBean acctDataBean, String acctNo) throws Exception
  {
    System.out.println("AcctNo");
    Date dateRegisterDate = null;
    Date dateExpiryDate = null;
    String emailStatus = "";
    String status = "";
    Date currentDate = new Date();
    Date dateRegisteredDate = acctDataBean.getRegisterDate();
    
	emailStatus = acctDataBean.getEmailStatus();
	//acctDataBean = AcctDetails.getLatestEmailDeliveryStatus(connection, strAccNo);
   /* if (dateRegisteredDate != null)
    {
      dateExpiryDate = dateRegisteredDate;
      Calendar c = Calendar.getInstance();
      c.setTime(dateExpiryDate);
      c.add(5, 5);
      dateExpiryDate = c.getTime();

      if (("P".equals(acctDataBean.getEmailSubscription())) && (currentDate.after(dateExpiryDate))) {
        String strEmailSubscription = "0";

        AcctDetails.updateEmailDeliverySubscription(acctNo, strEmailSubscription);
        acctDataBean.setEmailSubscription("0");
      }
    }
  }*/
	if((emailStatus.equals("Update")) ||(emailStatus.equals("Subscription"))){
		
	    dateRegisterDate = acctDataBean.getRegisterDate();
		if (dateRegisterDate != null) {
			
			
			dateExpiryDate = dateRegisterDate;

			Calendar c = Calendar.getInstance();
			c.setTime(dateExpiryDate);
			c.add(Calendar.DATE, 5);
			dateExpiryDate = c.getTime();
			
			if (currentDate.after(dateExpiryDate)) {
				
				 acctDataBean =AcctDetails.getAcctDetails(acctNo);
				
			 
			
		     }else{
			     status = "true";
		    	 acctDataBean.setEmailSubscription("P");
		    	 acctDataBean.setSMSNo(AcctDetails.getSMSNo(acctNo));
		    	 acctDataBean.setSMSSubscription(AcctDetails.getSMSSubscription(acctNo));
		
		     }
		}
			
	}else{
		
		acctDataBean =AcctDetails.getAcctDetails(acctNo);
		
		
	}
  }
}